package com.xframework.security.dao;

import java.sql.SQLException;
import java.util.List;

import com.xframework.security.util.Constant;
import org.springframework.stereotype.Repository;

import com.xframework.dao.BaseDao;
import com.xframework.model.SqlParams;
import com.xframework.security.model.*;

@Repository
public class SecurityDao extends BaseDao implements SQL {

    public SecurityDao() {
    }

    /**
     * 查询账户数量
     *
     * @param account Account
     * @return int
     */
    final static String selectAccountNumSql = "select count(t1.ACCOUNT_ID) as num from SECURITY_ACCOUNT t1 where t1.ACCOUNT_ID is not null ";

    public int selectAccountNum(Account account) throws Exception {
        SqlParams sqlParams = SQL.getAccountSqlParams(selectAccountNumSql, account);
        return this.queryForInt(sqlParams);
    }

    /**
     * 查询账户
     *
     * @param account Account
     * @return List
     */
    final static String selectAccountListSql = "select t1.* from SECURITY_ACCOUNT t1 where t1.ACCOUNT_ID is not null ";

    public List<Account> selectAccountList(Account account) throws Exception {
        SqlParams sqlParams = SQL.getAccountSqlParams(selectAccountListSql, account);
        return this.queryForList(sqlParams);
    }

    final static String selectRoleAccountListSql = "select t1.* from SECURITY_ACCOUNT t1, SECURITY_ACCOUNT_ROLE t2 where t1.ACCOUNT_ID = t2.ACCOUNT_ID ";

    public List<Account> selectRoleAccountList(AccountRole accountRole) throws Exception {
        SqlParams sqlParams = SQL.getRoleAccountSqlParams(selectRoleAccountListSql, accountRole);
        return this.queryForList(sqlParams, Account.class);
    }

    /**
     * 查询账户扩展属性
     *
     * @param property Property
     * @return List
     */
    final static String selectAccountPropertyListSql = "select t1.* from SECURITY_ACCOUNT_PROPERTY t1 where t1.ACCOUNT_ID is not null ";

    public List<Property> selectAccountPropertyList(Property property) throws Exception {
        SqlParams sqlParams = SQL.getAccountPropertySqlParams(selectAccountPropertyListSql, property);
        return this.queryForList(sqlParams);
    }

    /**
     * 查询系统应用
     *
     * @param application
     * @return
     * @throws SQLException
     */

    final static String selectApplicationNumSql = "SELECT COUNT(1) AS NUM FROM SECURITY_APPLICATION T1 WHERE T1.APPLICATION_NAME IS NOT NULL ";

    public int selectApplicationNum(Application application) throws Exception {
        SqlParams sqlParams = SQL.getApplicationSqlParams(selectApplicationNumSql, application);
        return this.queryForInt(sqlParams);
    }

    /**
     * 查询系统应用
     *
     * @param application
     * @return
     * @throws SQLException
     */
    final static String selectApplicationListSql = "select * from SECURITY_APPLICATION t1 where t1.APPLICATION_NAME IS NOT NULL ";

    public List<Application> selectApplicationList(Application application) throws Exception {
        SqlParams sqlParams = SQL.getApplicationSqlParams(selectApplicationListSql, application);
        sqlParams.addSql("order by t1.application_id");
        return this.queryForList(sqlParams);
    }

    /**
     * 通过上级资源id和系统id查询资源
     *
     * @return List
     */
    final static String selectResourceListSql = "select * from SECURITY_RESOURCE t1 where t1.RESOURCE_ID > " + Constant.MENU_INIT_ID + " ";

    public List<Resource> selectResourceList(Resource resource) throws Exception {
        SqlParams sqlParams = SQL.getResourceSqlParams(selectResourceListSql, resource);
        return this.queryForList(sqlParams);
    }

    /**
     * 查询账户权限资源
     *
     * @return List
     */
    final static String selectResourceByAccountApplicationSql = "select distinct t4.* ,t3.RESOURCE_SEQ AS SEQ from " +
            "SECURITY_ACCOUNT_ROLE  t2, " +
            "SECURITY_ROLE_RESOURCE t3, " +
            "SECURITY_RESOURCE      t4 " +
            "where t4.APPLICATION_ID = ? " +
            "and t4.RESOURCE_STATE = 1 " +
            "and t3.RESOURCE_ID = t4.RESOURCE_ID " +
            "and t2.ROLE_ID = t3.ROLE_ID ";

    public List<Resource> selectResourceByAccountApplication(SecurityAccount securityAccount) throws Exception {
        SqlParams sqlParams = SQL.getResourceByAccountApplicationSqlParams(selectResourceByAccountApplicationSql, securityAccount);
        return this.queryForList(sqlParams, Resource.class);
    }

    /**
     * 查询角色资源
     *
     * @return List
     */
    final static String selectRoleResourceListSql = "select t2.* from SECURITY_ROLE_RESOURCE t1, SECURITY_RESOURCE t2 where t1.RESOURCE_ID = t2.RESOURCE_ID ";

    public List<Resource> selectRoleResourceList(RoleResource roleResource) throws Exception {
        SqlParams sqlParams = SQL.getRoleResourceSqlParams(selectRoleResourceListSql, roleResource);
        return this.queryForList(sqlParams, Resource.class);
    }

    final static String selectResourceTypeListSql = "select * from SECURITY_RESOURCE_TYPE t1";

    public List<ResourceType> selectResourceTypeList(ResourceType resourceType) throws Exception {
        SqlParams sqlParams = SQL.getResourceTypeSqlParams(selectResourceTypeListSql, resourceType);
        return this.queryForList(sqlParams);
    }

    /**
     * 查询角色
     *
     * @param role
     * @return
     * @throws SQLException
     */
    final static String selectRoleNumSql = "SELECT COUNT(1) AS NUM FROM SECURITY_ROLE T1 WHERE 1 = 1 ";

    public int selectRoleNum(Role role) throws Exception {
        SqlParams sqlParams = SQL.getRoleSqlParams(selectRoleNumSql, role);
        return this.queryForInt(sqlParams);
    }

    public int selectThirdRoleNum(ThirdRole thirdrole) throws Exception {
        SqlParams sqlParams = SQL.getThirdRoleSqlParams(selectRoleNumSql, thirdrole);
        return this.queryForInt(sqlParams);
    }

    /**
     * 查询角色
     *
     * @param role
     * @return
     * @throws SQLException
     */
    final static String selectRoleListSql = "select * from SECURITY_ROLE t1 where 1 = 1 ";

    public List<Role> selectRoleList(Role role) throws Exception {
        SqlParams sqlParams = SQL.getRoleSqlParams(selectRoleListSql, role);
        return this.queryForList(sqlParams);
    }

    public List<Role> selectThirdRoleList(ThirdRole thirdRole) throws Exception {
        SqlParams sqlParams = SQL.getThirdRoleSqlParams(selectRoleListSql, thirdRole);
        return this.queryForList(sqlParams, Role.class);
    }

    /**
     * 查询账户角色
     *
     * @return List
     */
    final static String selectAccountRoleListSql = "select t2.* from SECURITY_ACCOUNT_ROLE t1, SECURITY_ROLE t2 where t1.ACCOUNT_ID = ? and t1.ROLE_ID = t2.ROLE_ID";

    public List<Role> selectAccountRoleList(AccountRole accountRole) throws Exception {
        return this.queryForList(selectAccountRoleListSql, Role.class, accountRole, accountRole.getAccountId());
    }

}
